***********************************************************************************
********"Macroprudential Regulation, Quantitative Easing, and Bank Lending"********
***********************************************************************************
* This file produces some of the datasets that will be used for the 2019 analyses
***********************************************************************************
***********************************************************************************


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/cbabser2019, case(lower)
rename ente_segn bankid
sort bankid

tostring(data_oss), replace
gen xxx=date(data_oss, "YMD")
drop data_oss

gen time=mofd(xxx)
drop xxx
format time %tm

rename valore cbabser
format %20.0g cbabser

merge m:1 bankid using temp, keepus(exbdisbur)
keep if _merge==3
drop _merge exbdisbur

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) cbabser, by(bankid time)

keep if time>=tm(2017m8)&time<=tm(2019m8)
egen xxx=max(cbabser), by(bankid)
duplicates drop bankid, force
gen excbabserd0=(xxx>0)
drop time xxx

keep bankid excbabserd0

**# Bookmark (saving dataset - covered bonds and asset backed securities exposure
* dummy as defined in Appendix II)
compress
save $inputdirectory/INB_cbabser2019, replace


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/isins2019, case(lower)

rename dat time
rename date_mat time_mat
rename ente_segn bankid
rename fonte source
egen isin=group(cod_isin)
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
tostring(time_mat), replace
gen xxx=date(time_mat, "YMD")
rename time_mat oldtime_mat
gen time_mat=mofd(xxx)
drop xxx
format time_mat %tm
replace time_mat=tm(2200m12) if time_mat==tm(9999m12)
gen monthtom=time_mat-time
drop if monthtom<0

replace book=-book if portaf_contab==508|portaf_contab==509
gen portfolio=.
replace portfolio=1 if inlist(portaf_contab,403,405,406)
replace portfolio=2 if inlist(portaf_contab,407)
replace portfolio=3 if inlist(portaf_contab,401,402,0,408)
drop if portfolio==.

fillin bankid time portfolio
drop if portfolio==.
replace isin=0 if isin==.
replace book=0 if book==.

egen id=group(isin bankid portfolio)
collapse (sum) book (first) cod_isin isin bankid portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)
xtset id time

merge m:1 bankid time using $inputdirectory/INB_assetsi, keepus(assets)
keep if _merge==3
drop _merge

merge m:1 bankid using temp, keepus(exbdisbur)
keep if _merge==3
drop _merge exbdisbur

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop id
drop bankid
rename gru bankid

egen id=group(isin bankid portfolio)
collapse (sum) book assets (first) cod_isin bankid isin portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)

xtset id time

keep if time==tm(2019m8)
* Keep securities not valued at historical cost
keep if portfolio==3|portfolio==2

gen cbabs=0
replace cbabs=1 if  inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974)&inlist(stanziabilita,23,24)&inlist(codvecom,"400H","420A","420B","420C")

gen book2=book
replace book=0 if cbabs!=1

collapse (sum) book book2 (max) assets, by(bankid)
gen excbabs4=100*(book/assets)

gen excbabs4d0=(excbabs4>0)
keep bankid excbabs4*

**# Bookmark (saving dataset - covered bonds and asset backed securities exposure
* as defined in Appendix II)
compress
save $inputdirectory/INB_cbabs42019, replace


*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/isins2019, case(lower)

rename dat time
rename date_mat time_mat
rename ente_segn bankid
rename fonte source
egen isin=group(cod_isin)
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
tostring(time_mat), replace
gen xxx=date(time_mat, "YMD")
rename time_mat oldtime_mat
gen time_mat=mofd(xxx)
drop xxx
format time_mat %tm
replace time_mat=tm(2200m12) if time_mat==tm(9999m12)
gen monthtom=time_mat-time
drop if monthtom<0

replace book=-book if portaf_contab==508|portaf_contab==409
gen portfolio=.
replace portfolio=1 if inlist(portaf_contab,403,405,406)
replace portfolio=2 if inlist(portaf_contab,407)
replace portfolio=3 if inlist(portaf_contab,401,402,0,408)
drop if portfolio==.

fillin bankid time portfolio
drop if portfolio==.
replace isin=0 if isin==.
replace book=0 if book==.

egen id=group(isin bankid portfolio)
collapse (sum) book (first) cod_isin isin bankid portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)
xtset id time

merge m:1 bankid time using $inputdirectory/INB_assetsi, keepus(assets)
keep if _merge==3
drop _merge

merge m:1 bankid using temp, keepus(exbdisbur)
keep if _merge==3
drop _merge exbdisbur

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop id
drop bankid
rename gru bankid 
egen id=group(isin bankid portfolio)
collapse (sum) book assets (first) cod_isin isin bankid portfolio codvecom (max) staemitit setemi stanziabilita time_mat monthtom _fillin, by(id time)

xtset id time

keep if time==tm(2019m8)
* keep assets not valued at historical cost
keep if portfolio==3|portfolio==2

gen cspp=0
replace cspp=1 if  inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,166,165,176,450,432,477,475,476,430,288,757)&inlist(stanziabilita,23,24)&(monthtom>=9)&(monthtom<=375)&(monthtom!=.)&time==tm(2019m8)

gen book2=book
replace book=0 if cspp!=1

collapse (sum) book book2 (max) assets, by(bankid)
gen excspp4=100*(book/assets)

keep bankid excspp4

**# Bookmark (saving dataset -  cspp exposure as defined in Appendix II)
compress
save $inputdirectory/INB_cspp42019, replace

*==================================================================================
**# Bookmark (input)
clear *
import sas using $inputdirectory/assets, case(lower)

rename dat time
tostring(time), replace
gen xxx=date(time, "YMD")
rename time oldtime
gen time=mofd(xxx)
drop xxx
format time %tm
format assets %20.0g
rename ente_segn bankid
drop oldtime

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) assets, by(bankid time)
xtset bankid time

**# Bookmark (saving dataset - total assets)
compress
save $inputdirectory/INB_assets2019, replace

*==================================================================================
**# Bookmark (input)
clear *

import excel using "$inputdirectory/Dati lavorati 1905_06_07", first clear
keep abi PDM CurrentAccount MinimumResereRequirementROB Depositfacilities
rename CurrentAccount caccount
rename MinimumResereRequirementROB min 
rename Depositfacilities ecbdepomp
rename abi bankid

gen time=tm(2019m9) if PDM==201905
replace time=tm(2019m10) if PDM==201906
replace time=tm(2019m12) if PDM==201907
format time %tm
drop PDM
drop if bankid==.

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) caccount min ecbdepomp, by(bankid time)

merge 1:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge

gen excess=caccount-min
gen depoexcess=ecbdepomp+excess
gen exunusallow=100*(6*min-depoexcess)/(assets/1000000)
replace exunusallow=0 if exunusallow<0 

keep if time==tm(2019m9)
keep bankid exunusallow

**# Bookmark (saving dataset - unused reserve allowance as defined 
* in Appendix II)
compress
save $inputdirectory/INB_unsuallow2019, replace


*==================================================================================
**# Bookmark (input)
clear *
clear all
import sas using $inputdirectory/ecbdepo, case(lower)

rename dat date
tostring(date), replace
gen xxx=date(date, "YMD")
drop date
gen time=mofd(xxx)
drop xxx
format time %tm
rename ente_segn bankid
drop abi

merge m:1 bankid using $inputdirectory/INB_gru2019, keepus(gru)
keep if _merge!=2
drop _merge
replace gru=bankid if gru==.
drop bankid
rename gru bankid
collapse (sum) ecbdepo, by(bankid time)

**# Bookmark (saving dataset - deposits at the ECB)
compress
save $inputdirectory/INB_ecbdepo2019, replace
